﻿using MKAdmin.DataHelper;
using MKAdmin.DTO.Web.Common;
using MKAdmin.DTO.Web.Table.BasicTable;
using MKAdmin.IService.Web.Table;
using MKAdmin.ToolKit;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace MKAdmin.ServiceImpl.Web.Table
{
    public class BasicTableService : IBasicTableService
    {
        /// <summary>
        /// 获取员工信息列表
        /// </summary>
        /// <param name="userInfo"></param>
        /// <returns></returns>
        public PagingList<GetBasicTableListModel> List(GetBasicTableListParameter parameter)
        {
            var list = new PagingList<GetBasicTableListModel>();

            using (var util = new DBHelper())
            {
                StringBuilder sql = new StringBuilder();
                sql.AppendLine(@"SELECT EmployeeId, EmployeeName,EmployeeSex,EmployeeAge
                                        ,EmployeeHeight,ContactPhone,Education
                                        ,EntryTime,CreateTime 
                                        FROM DBO.EmployeeInfo eli With(Nolock) 
                                Where Status In (0,1)");

                List<SqlParameter> param = new List<SqlParameter>();
                if (!string.IsNullOrEmpty(parameter.name))
                {
                    sql.AppendLine("And eli.EmployeeName Like '%'+@EmployeeName+'%'");
                    param.Add(new SqlParameter() { ParameterName = "@EmployeeName", DbType = DbType.String, Value = parameter.name });
                }

                string sortingSql = "Order By EmployeeId Desc";
                if (!string.IsNullOrEmpty(parameter.sortName) && !string.IsNullOrEmpty(parameter.sortOrder))
                {
                    sortingSql = $"Order By {parameter.sortName} {parameter.sortOrder}";
                }
                string sqlPaging = SqlPagingHelper.GetPagingSql(sql.ToString(), parameter.pageIndex, parameter.pageSize, sortingSql);
                list = util.GetDataTable(sqlPaging, CommandType.Text, param.ToArray()).ConvertToPagingList<GetBasicTableListModel>();

                return list;
            }
        }

        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="parameter"></param>
        /// <param name="userInfo"></param>
        /// <returns></returns>
        public Result Add(AddBasicTableParameter parameter, UserInfoModel userInfo)
        {
            var result = new Result() { msg = "新增成功" };

            using (var util = new DBHelper())
            {
                StringBuilder sql = new StringBuilder();
                sql.AppendLine(@"INSERT INTO [dbo].[EmployeeInfo]
                                       ([EmployeeName],[EmployeeSex],[EmployeeAge],[EmployeeHeight]
		                               ,[EmployeeWeight],[PayMoney],[HeadFileName],[ContactPhone]
                                       ,[Education],[AddressDetail],[Hobby],[Motto]
		                               ,CreatorId,[StatusCode],[EntryTime],[CreateTime]
		                               ,[UpdateTime],[Remark])
                                 VALUES
                                       (@EmployeeName,@EmployeeSex,@EmployeeAge,@EmployeeHeight
                                       ,55,0,'',@ContactPhone
                                       ,@Education,'','',''
                                       ,@CreatorId,0,getdate(),getdate()
		                               ,getdate(),'')");

                List<SqlParameter> param = new List<SqlParameter>();
                param.Add(new SqlParameter() { ParameterName = "@EmployeeName", DbType = DbType.String, Value = parameter.employeeName });
                param.Add(new SqlParameter() { ParameterName = "@EmployeeSex", DbType = DbType.Int32, Value = parameter.employeeSex });
                param.Add(new SqlParameter() { ParameterName = "@EmployeeAge", DbType = DbType.Int32, Value = parameter.employeeAge });
                param.Add(new SqlParameter() { ParameterName = "@EmployeeHeight", DbType = DbType.Int32, Value = parameter.employeeHeight });
                param.Add(new SqlParameter() { ParameterName = "@ContactPhone", DbType = DbType.String, Value = parameter.employeePhone });
                param.Add(new SqlParameter() { ParameterName = "@Education", DbType = DbType.String, Value = parameter.employeeEducation });
                param.Add(new SqlParameter() { ParameterName = "@CreatorId", DbType = DbType.Int32, Value = userInfo.OperatorId });

                int _r = util.ExecuteQuery(sql.ToString(), CommandType.Text, param.ToArray());
                if (_r <= 0)
                {
                    result.status = false;
                    result.msg = "新增失败";
                }
                return result;
            }
        }

        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="parameter"></param>
        /// <param name="userInfo"></param>
        /// <returns></returns>
        public Result Edit(EditBasicTableParameter parameter)
        {
            var result = new Result() { msg = "修改成功" };

            using (var util = new DBHelper())
            {
                StringBuilder sql = new StringBuilder();
                sql.AppendLine(@"Update [dbo].[EmployeeInfo]
                                 Set [EmployeeName] = @EmployeeName
                                     ,[EmployeeSex] = @EmployeeSex
                                     ,[EmployeeAge] = @EmployeeAge
                                     ,[EmployeeHeight] = @EmployeeHeight
		                             ,[ContactPhone] = @ContactPhone
                                     ,[Education] = @Education
		                             ,[UpdateTime] = GetDate()
                                 Where EmployeeId = @EmployeeId");

                List<SqlParameter> param = new List<SqlParameter>();
                param.Add(new SqlParameter() { ParameterName = "@EmployeeId", DbType = DbType.Int32, Value = parameter.employeeId });
                param.Add(new SqlParameter() { ParameterName = "@EmployeeName", DbType = DbType.String, Value = parameter.employeeName });
                param.Add(new SqlParameter() { ParameterName = "@EmployeeSex", DbType = DbType.Int32, Value = parameter.employeeSex });
                param.Add(new SqlParameter() { ParameterName = "@EmployeeAge", DbType = DbType.Int32, Value = parameter.employeeAge });
                param.Add(new SqlParameter() { ParameterName = "@EmployeeHeight", DbType = DbType.Int32, Value = parameter.employeeHeight });
                param.Add(new SqlParameter() { ParameterName = "@ContactPhone", DbType = DbType.String, Value = parameter.employeePhone });
                param.Add(new SqlParameter() { ParameterName = "@Education", DbType = DbType.String, Value = parameter.employeeEducation });

                int _r = util.ExecuteQuery(sql.ToString(), CommandType.Text, param.ToArray());
                if (_r <= 0)
                {
                    result.status = false;
                    result.msg = "修改失败";
                }
                return result;
            }
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="parameter"></param>
        /// <param name="userInfo"></param>
        /// <returns></returns>
        public Result Del(DelBasicTableParameter parameter)
        {
            var result = new Result() { msg = "删除成功" };

            using (var util = new DBHelper())
            {
                StringBuilder sql = new StringBuilder();
                sql.AppendLine($@"Update [dbo].[EmployeeInfo]
                                  Set Status = 2 , UpdateTime = GetDate()
                                  Where EmployeeId = {parameter.employeeId}");


                int _r = util.ExecuteQuery(sql.ToString());
                if (_r <= 0)
                {
                    result.status = false;
                    result.msg = "删除失败";
                }
                return result;
            }
        }
    }
}
